/**
 * @author: zouye<yzyngz@foxmail.com>
 * @date: 2021-12-19 18:12:46
 * @copyright: zouye<yzyngz@foxmail.com>
 */
package com.yeah.study.easyoffice.service;

import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.yeah.study.easyoffice.dto.EasyPoiNeedMergeVo;
import com.yeah.study.easyoffice.dto.EasyPoiVo;
import com.yeah.study.easyoffice.dto.ExcelMergeVo;
import com.yeah.study.easyoffice.dto.ExcelVo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.util.PoiPublicUtil;

/**
 * Excel 处理类
 */
@Service
public class ExcelService {
	
	private List<ExcelMergeVo>getMergeData() {
		List<ExcelMergeVo> list = new ArrayList<>();
		for (int i = 0; i < 10; i++) {
			ExcelMergeVo vo = new ExcelMergeVo(i, "列" + i, new Date(), 2F);
			list.add(vo);
		}
		
		return list;
	}
	
	private List<ExcelVo> getData(Integer count) {
		List<ExcelVo> list = new ArrayList<>();
		for (int i = 0; i < count; i++) {
			ExcelVo vo = new ExcelVo("列列" + i, 8F, i);
			list.add(vo);
		}
		
		return list;
	}
	
	private List<EasyPoiNeedMergeVo>getEasyPoiMergeData() {
		List<EasyPoiNeedMergeVo> list = new ArrayList<>();
		for (int i = 0; i < 10; i++) {
			EasyPoiNeedMergeVo vo = new EasyPoiNeedMergeVo(i, "列" + i, new Date(), 2F);
			vo.setSuitList(getEasyPoiData(i + 1));
			list.add(vo);
		}
		
		return list;
	}
	
	private List<EasyPoiVo> getEasyPoiData(Integer count) {
		List<EasyPoiVo> list = new ArrayList<>();
		for (int i = 0; i < count; i++) {
			EasyPoiVo vo = new EasyPoiVo("列列" + i, 8F, i);
			list.add(vo);
		}
		
		return list;
	}
	
	/**
	 * 使用 Easy-Poi 导出合并单元格的情况
	 * @param response
	 * @throws Exception
	 */
	public void useEasyPoiMergeExcel(HttpServletResponse response) throws Exception {
		String title = "产品信息统计";
		List<EasyPoiNeedMergeVo> list = getEasyPoiMergeData();
        ExportParams params = new ExportParams();
        params.setSheetName(title);     //设置sheet名
        Workbook workbook = ExcelExportUtil.exportExcel(params, EasyPoiNeedMergeVo.class, list);
        
        String fileName = URLEncoder.encode("测试合并单元格", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
		OutputStream outStream = null;
		try {
			outStream = response.getOutputStream();
			workbook.write(outStream);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			outStream.flush();
			outStream.close();
		}
	}
	

    /**
     * 下载 ExcelEntity 数据
     * @author zouye
     * @param <T>
     * @param response
     * @param beanClazz
     */
    public <T> void exportExcelData(HttpServletResponse response, String fileName, List<T> data, Class<T> beanClazz) throws Exception {
        setDownloadHeader(response, fileName);
        
        downloadExcelData(response, data, beanClazz);
    }
    
    /**
     * 下载 ExcelEntity 模板
     * @author zouye
     * @param response
     */
    public <T> void exportExcelTemplate(HttpServletResponse response, String fileName, Class<T> beanClazz) throws Exception {
        setDownloadHeader(response, fileName);
        List<T> list = new ArrayList<>();
        list.add(beanClazz.getDeclaredConstructor().newInstance());
        downloadExcelData(response, list, beanClazz);
    }
    
    /**
     * 使用 ExcelEntity 模板导出数据，模板中使用表达式语法。
     * 支持合计操作，支持合并单元格
     * TODO: 未完待续
     * @author zouye
     * @param <T>
     * @param response
     * @param excel 模板的本地文件系统路径或http路径
     * @param fileName 导出的文件名称
     * @param dataMap 数据，key 和模板中的表达式对应
     */
    public <T> void exportExcelDataFromTemplate(HttpServletResponse response,
            String templateUrl, String fileName, Map<String, Object> dataMap, MergeConf mergeConf) throws Exception {
        if (dataMap == null || dataMap.isEmpty()) {
            throw new RuntimeException("数据不能为空");
        }
        
        setDownloadHeader(response, fileName);
        TemplateExportParams params = new TemplateExportParams(templateUrl);
        
        Workbook workbook = ExcelExportUtil.exportExcel(params, dataMap);
        if (mergeConf != null && workbook.getNumberOfSheets() > 0) {
            for (Integer col: mergeConf.getColumns()) {
                for(Integer[] rowRange: mergeConf.getRowRanges()) {
                    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowRange[0], rowRange[1], col, col));
                }
            }
        }
        
        OutputStream outStream = null;
        try {
            outStream = response.getOutputStream();
            workbook.write(outStream);
        } catch (Exception e) {
            throw e;
        } finally {
            if (outStream != null) {
                outStream.flush();
                outStream.close();
            }
        }
    }
    
    /**
     * 使用 ExcelEntity 模板导出数据，模板中使用表达式语法。
     * 支持合计操作，支持合并单元格
     * TODO: 未完待续
     * @author zouye
     * @param <T>
     * @param response
     * @param excel 模板的本地文件系统路径或http路径
     * @param fileName 导出的文件名称
     * @param beanList 实体类数据列表
     * @param beanClazz
     */
    public <T> void exportExcelDataFromTemplate(HttpServletResponse response,
            String templateUrl, String fileName, List<T> beanList, Class<T> beanClazz) throws Exception {
        
        if (beanList == null || beanList.isEmpty()) {
            exportExcelTemplate(response, fileName, beanClazz);
            return;
        }
        
        // 将 bean 的字段名称转为对应的 map
        Field[] fields = PoiPublicUtil.getClassFields(beanClazz);
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            /*
             * if (field.getAnnotation(ExcelEntity.class) != null) {
             * 
             * }
             */
        }
    }
    
    
    /**
     * 设置文件头
     * @author zouye
     * @param response
     * @param fileName
     * @throws UnsupportedEncodingException 
     */
    public void setDownloadHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
    }
    
    /**
     * 不设置响应头
     * @author zouye
     * @param <T>
     * @param response
     * @param data
     * @param beanClazz
     * @throws Exception 
     */
    private <T> void downloadExcelData(HttpServletResponse response, List<T> data, Class<T> beanClazz) throws Exception {
        ExportParams params = new ExportParams();
        params.setSheetName("Sheet1");
        params.setHeaderHeight(20);
        Workbook workbook = ExcelExportUtil.exportExcel(params, beanClazz, data);
        
        OutputStream outStream = null;
        try {
            outStream = response.getOutputStream();
            workbook.write(outStream);
        } catch (Exception e) {
            throw e;
        } finally {
            if (outStream != null) {
                outStream.flush();
                outStream.close();
            }
        }
    }
    
    /**
     * 从 excel 中读取数据，返回一个 bean list
     * @author zouye
     * @param <T>
     * @param inputStream
     * @param beanClazz
     * @return
     * @throws Exception 
     */
    public <T> List<T> readExcel(InputStream inputStream, Class<T> beanClazz) throws Exception {
        ImportParams params = new ImportParams();
        params.setStartSheetIndex(0);
        params.setSheetNum(1);
        return ExcelImportUtil.importExcel(inputStream, beanClazz, params);
    }
	
	
	public void mergeColumn(HttpServletResponse response) throws Exception {
		List<ExcelMergeVo> mergeList = getMergeData();
		
		String fileName = URLEncoder.encode("测试合并单元格", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        ExcelWriter excelWriter = null;
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        try {
            excelWriter = EasyExcel.write(response.getOutputStream(), ExcelMergeVo.class).registerWriteHandler(null).build();
            excelWriter.write(mergeList, writeSheet);
            for (int i = 0; i < mergeList.size(); i++) {
            	
            }
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
	}
}
